<!DOCTYPE html>
<html lang="zh-CN">
    <head>
        <meta charset="utf-8">
        <meta name="viewport" content="width=device-width, initial-scale=1">
        <meta name="robots" content="noodp" />
        <meta http-equiv="X-UA-Compatible" content="IE=edge, chrome=1">
        <title>Mysql检测工具使用 - 德国粗茶淡饭</title><meta name="Description" content="Mysql检测工具使用"><meta property="og:title" content="Mysql检测工具使用" />
<meta property="og:description" content="Mysql检测工具使用" />
<meta property="og:type" content="article" />
<meta property="og:url" content="https://www.ctq6.cn/technology/mysql/mysql%E6%A3%80%E6%B5%8B%E5%B7%A5%E5%85%B7%E4%BD%BF%E7%94%A8/" />
<meta property="og:image" content="https://www.ctq6.cn/logo.png"/>
<meta property="article:published_time" content="2021-02-16T19:39:00+08:00" />
<meta property="article:modified_time" content="2021-02-16T19:39:00+08:00" />
<meta name="twitter:card" content="summary_large_image"/>
<meta name="twitter:image" content="https://www.ctq6.cn/logo.png"/>

<meta name="twitter:title" content="Mysql检测工具使用"/>
<meta name="twitter:description" content="Mysql检测工具使用"/>
<meta name="application-name" content="LoveIt">
<meta name="apple-mobile-web-app-title" content="LoveIt"><meta name="theme-color" content="#ffffff"><meta name="msapplication-TileColor" content="#da532c"><link rel="shortcut icon" type="image/x-icon" href="/favicon.ico" />
        <link rel="icon" type="image/png" sizes="32x32" href="/favicon-32x32.png">
        <link rel="icon" type="image/png" sizes="16x16" href="/favicon-16x16.png"><link rel="apple-touch-icon" sizes="180x180" href="/apple-touch-icon.png"><link rel="mask-icon" href="/safari-pinned-tab.svg" color="#5bbad5"><link rel="manifest" href="/site.webmanifest"><link rel="canonical" href="https://www.ctq6.cn/technology/mysql/mysql%E6%A3%80%E6%B5%8B%E5%B7%A5%E5%85%B7%E4%BD%BF%E7%94%A8/" /><link rel="prev" href="https://www.ctq6.cn/technology/mysql/mysql%E6%89%A7%E8%A1%8C%E8%BF%87%E7%A8%8B/" /><link rel="next" href="https://www.ctq6.cn/technology/mysql/mysql%E7%B4%A2%E5%BC%95/" /><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/normalize.css@8.0.1/normalize.min.css"><link rel="stylesheet" href="/css/style.min.css"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fortawesome/fontawesome-free@5.13.0/css/all.min.css"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/animate.css@3.7.2/animate.min.css"><script type="application/ld+json">
    {
        "@context": "http://schema.org",
        "@type": "BlogPosting",
        "headline": "Mysql检测工具使用",
        "inLanguage": "zh-CN",
        "mainEntityOfPage": {
            "@type": "WebPage",
            "@id": "https:\/\/www.ctq6.cn\/technology\/mysql\/mysql%E6%A3%80%E6%B5%8B%E5%B7%A5%E5%85%B7%E4%BD%BF%E7%94%A8\/"
        },"image": [{
                            "@type": "ImageObject",
                            "url": "https:\/\/www.ctq6.cn\/images\/Apple-Devices-Preview.png",
                            "width":  3200 ,
                            "height":  2048 
                        }],"genre": "technology","keywords": "Mysql","wordcount":  5095 ,
        "url": "https:\/\/www.ctq6.cn\/technology\/mysql\/mysql%E6%A3%80%E6%B5%8B%E5%B7%A5%E5%85%B7%E4%BD%BF%E7%94%A8\/","datePublished": "2021-02-16T19:39:00+08:00","dateModified": "2021-02-16T19:39:00+08:00","license": "This work is licensed under a Creative Commons Attribution-NonCommercial 4.0 International License.","publisher": {
            "@type": "Organization",
            "name": "xxxx","logo": {
                    "@type": "ImageObject",
                    "url": "https:\/\/www.ctq6.cn\/images\/avatar.png",
                    "width":  528 ,
                    "height":  560 
                }},"author": {
                "@type": "Person",
                "name": "MikelPan"
            },"description": "Mysql检测工具使用"
    }
    </script></head>
    <body header-desktop="fixed" header-mobile="auto"><script type="text/javascript">(window.localStorage && localStorage.getItem('theme') ? localStorage.getItem('theme') === 'dark' : ('auto' === 'auto' ? window.matchMedia('(prefers-color-scheme: dark)').matches : 'auto' === 'dark')) && document.body.setAttribute('theme', 'dark');</script>

        <div id="mask"></div><div class="wrapper"><header class="desktop" id="header-desktop">
    <div class="header-wrapper">
        <div class="header-title">
            <a href="/" title="德国粗茶淡饭"><span class="header-title-pre"><i class='far fa-kiss-wink-heart fa-fw'></i></span>德国粗茶淡饭</a>
        </div>
        <div class="menu">
            <div class="menu-inner"><a class="menu-item" href="/posts/"> 生活 </a><a class="menu-item" href="/technology/"> 技术 </a><a class="menu-item" href="/tags/"> 标签 </a><a class="menu-item" href="/categories/"> 分类 </a><a class="menu-item" href="/categories/documentation/"> 文档 </a><a class="menu-item" href="/about/"> 关于 </a><a class="menu-item" href="https://github.com/MikelPan/Cnblog.git" title="GitHub" rel="noopener noreffer" target="_blank"><i class='fab fa-github fa-fw'></i>  </a><span class="menu-item delimiter"></span><a href="javascript:void(0);" class="menu-item language" title="选择语言">简体中文<i class="fas fa-chevron-right fa-fw"></i>
                        <select class="language-select" id="language-select-desktop" onchange="location = this.value;"><option value="/technology/mysql/mysql%E6%A3%80%E6%B5%8B%E5%B7%A5%E5%85%B7%E4%BD%BF%E7%94%A8/" selected>简体中文</option></select>
                    </a><span class="menu-item search" id="search-desktop">
                        <input type="text" placeholder="搜索文章标题或内容..." id="search-input-desktop">
                        <a href="javascript:void(0);" class="search-button search-toggle" id="search-toggle-desktop" title="搜索">
                            <i class="fas fa-search fa-fw"></i>
                        </a>
                        <a href="javascript:void(0);" class="search-button search-clear" id="search-clear-desktop" title="清空">
                            <i class="fas fa-times-circle fa-fw"></i>
                        </a>
                        <span class="search-button search-loading" id="search-loading-desktop">
                            <i class="fas fa-spinner fa-fw fa-spin"></i>
                        </span>
                    </span><a href="javascript:void(0);" class="menu-item theme-switch" title="切换主题">
                    <i class="fas fa-adjust fa-fw"></i>
                </a>
            </div>
        </div>
    </div>
</header><header class="mobile" id="header-mobile">
    <div class="header-container">
        <div class="header-wrapper">
            <div class="header-title">
                <a href="/" title="德国粗茶淡饭"><span class="header-title-pre"><i class='far fa-kiss-wink-heart fa-fw'></i></span>德国粗茶淡饭</a>
            </div>
            <div class="menu-toggle" id="menu-toggle-mobile">
                <span></span><span></span><span></span>
            </div>
        </div>
        <div class="menu" id="menu-mobile"><div class="search-wrapper">
                    <div class="search mobile" id="search-mobile">
                        <input type="text" placeholder="搜索文章标题或内容..." id="search-input-mobile">
                        <a href="javascript:void(0);" class="search-button search-toggle" id="search-toggle-mobile" title="搜索">
                            <i class="fas fa-search fa-fw"></i>
                        </a>
                        <a href="javascript:void(0);" class="search-button search-clear" id="search-clear-mobile" title="清空">
                            <i class="fas fa-times-circle fa-fw"></i>
                        </a>
                        <span class="search-button search-loading" id="search-loading-mobile">
                            <i class="fas fa-spinner fa-fw fa-spin"></i>
                        </span>
                    </div>
                    <a href="javascript:void(0);" class="search-cancel" id="search-cancel-mobile">
                        取消
                    </a>
                </div><a class="menu-item" href="/posts/" title="">生活</a><a class="menu-item" href="/technology/" title="">技术</a><a class="menu-item" href="/tags/" title="">标签</a><a class="menu-item" href="/categories/" title="">分类</a><a class="menu-item" href="/categories/documentation/" title="">文档</a><a class="menu-item" href="/about/" title="">关于</a><a class="menu-item" href="https://github.com/MikelPan/Cnblog.git" title="GitHub" rel="noopener noreffer" target="_blank"><i class='fab fa-github fa-fw'></i></a><a href="javascript:void(0);" class="menu-item theme-switch" title="切换主题">
                <i class="fas fa-adjust fa-fw"></i>
            </a><a href="javascript:void(0);" class="menu-item" title="选择语言">简体中文<i class="fas fa-chevron-right fa-fw"></i>
                    <select class="language-select" onchange="location = this.value;"><option value="/technology/mysql/mysql%E6%A3%80%E6%B5%8B%E5%B7%A5%E5%85%B7%E4%BD%BF%E7%94%A8/" selected>简体中文</option></select>
                </a></div>
    </div>
</header>
<div class="search-dropdown desktop">
    <div id="search-dropdown-desktop"></div>
</div>
<div class="search-dropdown mobile">
    <div id="search-dropdown-mobile"></div>
</div>
<main class="main">
                <div class="container"><div class="page single special"><h1 class="single-title animated pulse faster">Mysql检测工具使用</h1><div class="content" id="content"><h3 id="一mysqldumpslow工具使用">一、mysqldumpslow工具使用</h3>
<h4 id="11修改配置文件开启慢查询">1.1、修改配置文件开启慢查询</h4>
<p><strong>mysql 开启慢查询</strong></p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span><span class="lnt">4
</span><span class="lnt">5
</span><span class="lnt">6
</span><span class="lnt">7
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-shell" data-lang="shell">systemctl stop mysqld
<span class="nb">echo</span> -e <span class="s2">&#34;# 开启慢查询\nslow_query_log = 1\nslow_query_log_file = /var/lib/mysql/slow-query.log\nlong_query_time = 1\nlog_queries_not_using_indexes = 1&#34;</span> &gt;&gt;/etc/my.cnf
<span class="c1"># 重启mysql</span>
systemctl restart mysqld
<span class="c1"># 登录mysql</span>
mysql -uroot -pP@ssw0rd1
<span class="k">select</span> sleep<span class="o">(</span>1<span class="o">)</span><span class="p">;</span>
</code></pre></td></tr></table>
</div>
</div><h4 id="12修改变量开启慢查询">1.2、修改变量开启慢查询</h4>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-sql" data-lang="sql"><span class="k">set</span> <span class="k">global</span> <span class="n">slow_query_log</span><span class="o">=</span><span class="s1">&#39;ON&#39;</span><span class="p">;</span>
<span class="k">set</span> <span class="k">global</span> <span class="n">slow_query_log_file</span><span class="o">=</span><span class="s1">&#39;/var/lib/mysql/logs/slow.log&#39;</span><span class="p">;</span>
<span class="k">set</span> <span class="k">global</span> <span class="n">long_query_time</span><span class="o">=</span><span class="mi">1</span><span class="p">;</span>
</code></pre></td></tr></table>
</div>
</div><p><strong>使用mysqldumpslow 工具分析 慢查询日志</strong></p>
<ul>
<li>
<p>-s：排序方式，值如下
c：查询次数
t：查询时间
l：锁定时间
r：返回记录
ac：平均查询次数
al：平均锁定时间
ar：平均返回记录书
at：平均查询时间</p>
</li>
<li>
<p>-t：top N查询</p>
</li>
<li>
<p>-g：正则表达式</p>
</li>
</ul>
<p>1、访问次数最多的5个sql语句</p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span><span class="lnt">4
</span><span class="lnt">5
</span><span class="lnt">6
</span><span class="lnt">7
</span><span class="lnt">8
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-shell" data-lang="shell">mysqldumpslow -s c -t <span class="m">5</span> /var/lib/mysql/slow-query.log
----------------------------------start----------------------------------------
Reading mysql slow query log from /var/lib/mysql/slow-query.log
Count: <span class="m">2</span>  <span class="nv">Time</span><span class="o">=</span>1.50s <span class="o">(</span>3s<span class="o">)</span>  <span class="nv">Lock</span><span class="o">=</span>0.00s <span class="o">(</span>0s<span class="o">)</span>  <span class="nv">Rows</span><span class="o">=</span>1.0 <span class="o">(</span>2<span class="o">)</span>,
<span class="k">select</span> sleep<span class="o">(</span>N<span class="o">)</span>

Died at /usr/bin/mysqldumpslow line 161, &lt;&gt; chunk 2.
----------------------------------end-------------------------------------------
</code></pre></td></tr></table>
</div>
</div><h3 id="二mysqlsla工具使用">二、mysqlsla工具使用</h3>
<p><strong>mysqlsla安装</strong></p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span><span class="lnt">3
</span><span class="lnt">4
</span><span class="lnt">5
</span><span class="lnt">6
</span><span class="lnt">7
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-shell" data-lang="shell">wget http://hackmysql.com/scripts/mysqlsla-2.03.tar.gz
tar zxvf mysqlsla-2.03.tar.gz -C /usr/local/src
<span class="nb">cd</span> /usr/local/src/mysqlsla-2.03
yum install -y perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker
yum install -y perl-DBD-MySQL
perl Makefile.PL
make <span class="o">&amp;&amp;</span> make install
</code></pre></td></tr></table>
</div>
</div><p><strong>mysqlsla 分析慢查询日志</strong></p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt">1
</span><span class="lnt">2
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-shell" data-lang="shell">mysqlsla -lt slow -sf <span class="s2">&#34;+select,update,insert&#34;</span> -top <span class="m">10</span> slow.log &gt; /root/test_time.log
mysqlsla -lt slow -sf <span class="s2">&#34;+select,update,insert&#34;</span> -top <span class="m">10</span> -sort c_sum -db databasename slow.log &gt; /root/test_time.log
</code></pre></td></tr></table>
</div>
</div><p><strong>通过mysqlsla 查询日志分析</strong></p>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt"> 1
</span><span class="lnt"> 2
</span><span class="lnt"> 3
</span><span class="lnt"> 4
</span><span class="lnt"> 5
</span><span class="lnt"> 6
</span><span class="lnt"> 7
</span><span class="lnt"> 8
</span><span class="lnt"> 9
</span><span class="lnt">10
</span><span class="lnt">11
</span><span class="lnt">12
</span><span class="lnt">13
</span><span class="lnt">14
</span><span class="lnt">15
</span><span class="lnt">16
</span><span class="lnt">17
</span><span class="lnt">18
</span><span class="lnt">19
</span><span class="lnt">20
</span><span class="lnt">21
</span><span class="lnt">22
</span><span class="lnt">23
</span><span class="lnt">24
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-shell" data-lang="shell">mysqlsla -lt slow -sf <span class="s2">&#34;+select&#34;</span> -top <span class="m">10</span> /var/lib/mysql/slow-query.log
---------------------------------start--------------------------------------
Report <span class="k">for</span> slow logs: /var/lib/mysql/slow-query.log
<span class="m">2</span> queries total, <span class="m">1</span> unique
Sorted by <span class="s1">&#39;t_sum&#39;</span>
Grand Totals: Time <span class="m">3</span> s, Lock <span class="m">0</span> s, Rows sent 2, Rows Examined <span class="m">0</span>


______________________________________________________________________ <span class="m">001</span> ___
Count         : <span class="m">2</span>  <span class="o">(</span>100.00%<span class="o">)</span>
Time          : 3.001489 s total, 1.500745 s avg, 1.000509 s to 2.00098 s max  <span class="o">(</span>100.00%<span class="o">)</span>
Lock Time <span class="o">(</span>s<span class="o">)</span> : <span class="m">0</span> total, <span class="m">0</span> avg, <span class="m">0</span> to <span class="m">0</span> max  <span class="o">(</span>0.00%<span class="o">)</span>
Rows sent     : <span class="m">1</span> avg, <span class="m">1</span> to <span class="m">1</span> max  <span class="o">(</span>100.00%<span class="o">)</span>
Rows examined : <span class="m">0</span> avg, <span class="m">0</span> to <span class="m">0</span> max  <span class="o">(</span>0.00%<span class="o">)</span>
Database      :
Users         :
	root@localhost  : 100.00% <span class="o">(</span>2<span class="o">)</span> of query, 100.00% <span class="o">(</span>2<span class="o">)</span> of all users

Query abstract:
SELECT sleep<span class="o">(</span>N<span class="o">)</span><span class="p">;</span>

Query sample:
<span class="k">select</span> sleep<span class="o">(</span>1<span class="o">)</span><span class="p">;</span>
---------------------------------end--------------------------------------
</code></pre></td></tr></table>
</div>
</div><h3 id="三pt工具使用">三、pt工具使用</h3>
<h4 id="1pt-工具安装">1、pt 工具安装</h4>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt"> 1
</span><span class="lnt"> 2
</span><span class="lnt"> 3
</span><span class="lnt"> 4
</span><span class="lnt"> 5
</span><span class="lnt"> 6
</span><span class="lnt"> 7
</span><span class="lnt"> 8
</span><span class="lnt"> 9
</span><span class="lnt">10
</span><span class="lnt">11
</span><span class="lnt">12
</span><span class="lnt">13
</span><span class="lnt">14
</span><span class="lnt">15
</span><span class="lnt">16
</span><span class="lnt">17
</span><span class="lnt">18
</span><span class="lnt">19
</span><span class="lnt">20
</span><span class="lnt">21
</span><span class="lnt">22
</span><span class="lnt">23
</span><span class="lnt">24
</span><span class="lnt">25
</span><span class="lnt">26
</span><span class="lnt">27
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-shell" data-lang="shell"><span class="cp">#!/bin/bash
</span><span class="cp"></span>percona-toolkit-yum-install<span class="o">(){</span>
<span class="c1"># 下载最新版percona-toolkits 包</span>
下载地址：https://www.percona.com/downloads/
wget -P /tar https://www.percona.com/downloads/percona-toolkit/3.0.12/binary/redhat/7/x86_64/percona-toolkit-3.0.12-re3a693a-el7-x86_64-bundle.tar
tar xvf /tar/percona-toolkit-3.0.12-re3a693a-el7-x86_64-bundle.tar
<span class="c1"># 安装依赖</span>
yum install -y perl perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-Digest-MD5
rpm -ivh percona-toolkit-3.0.12-1.el7.x86_64.rpm
<span class="o">}</span>
percona-toolkit-unline-install<span class="o">(){</span>
<span class="c1"># 安装离线安装包</span>
rpm -ivh /percona-yum/*.rpm
rpm -ivh percona-toolkit-3.0.12-1.el7.x86_64.rpm
<span class="o">}</span>
--create-review-table  当使用--review参数把分析结果输出到表中时，如果没有表就自动创建
--create-history-table  当使用--history参数把分析结果输出到表中时，如果没有表就自动创建
--filter  对输入的慢查询按指定的字符串进行匹配过滤后再进行分析
--limit   限制输出结果百分比或数量，默认值是20,即将最慢的20条语句输出，如果是50%则按总响应时间占比从大到小排序，输出到总和达到50%位置截止。
--host  mysql服务器地址
--user  mysql用户名
--password  mysql用户密码
--history   将分析结果保存到表中，分析结果比较详细，下次再使用--history时，如果存在相同的语句，且查询所在的时间区间和历史表中的不同，则会记录到数据表中，可以通过查询同一CHECKSUM来比较某类型查询的历史变化。
--review 将分析结果保存到表中，这个分析只是对查询条件进行参数化，一个类型的查询一条记录，比较简单。当下次使用--review时，如果存在相同的语句分析，就不会记录到数据表中。
--output 分析结果输出类型，值可以是report<span class="o">(</span>标准分析报告<span class="o">)</span>、slowlog<span class="o">(</span>Mysql slow log<span class="o">)</span>、json、json-anon，一般使用report，以便于阅读。
--since 从什么时间开始分析，值为字符串，可以是指定的某个”yyyy-mm-dd <span class="o">[</span>hh:mm:ss<span class="o">]</span>”格式的时间点，也可以是简单的一个时间值：s<span class="o">(</span>秒<span class="o">)</span>、h<span class="o">(</span>小时<span class="o">)</span>、m<span class="o">(</span>分钟<span class="o">)</span>、d<span class="o">(</span>天<span class="o">)</span>，如12h就表示从12小时前开始统计。
--until 截止时间，配合—since可以分析一段时间内的慢查询
</code></pre></td></tr></table>
</div>
</div><h4 id="2percona-toolkit用法">2、percona-toolkit用法</h4>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt"> 1
</span><span class="lnt"> 2
</span><span class="lnt"> 3
</span><span class="lnt"> 4
</span><span class="lnt"> 5
</span><span class="lnt"> 6
</span><span class="lnt"> 7
</span><span class="lnt"> 8
</span><span class="lnt"> 9
</span><span class="lnt">10
</span><span class="lnt">11
</span><span class="lnt">12
</span><span class="lnt">13
</span><span class="lnt">14
</span><span class="lnt">15
</span><span class="lnt">16
</span><span class="lnt">17
</span><span class="lnt">18
</span><span class="lnt">19
</span><span class="lnt">20
</span><span class="lnt">21
</span><span class="lnt">22
</span><span class="lnt">23
</span><span class="lnt">24
</span><span class="lnt">25
</span><span class="lnt">26
</span><span class="lnt">27
</span><span class="lnt">28
</span><span class="lnt">29
</span><span class="lnt">30
</span><span class="lnt">31
</span><span class="lnt">32
</span><span class="lnt">33
</span><span class="lnt">34
</span><span class="lnt">35
</span><span class="lnt">36
</span><span class="lnt">37
</span><span class="lnt">38
</span><span class="lnt">39
</span><span class="lnt">40
</span><span class="lnt">41
</span><span class="lnt">42
</span><span class="lnt">43
</span><span class="lnt">44
</span><span class="lnt">45
</span><span class="lnt">46
</span><span class="lnt">47
</span><span class="lnt">48
</span><span class="lnt">49
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-shell" data-lang="shell"><span class="c1"># 查看慢查询日志</span>
pt-query-digest slow-query.log
--------------------------------------start---------------------------------------
<span class="c1"># 280ms user time, 40ms system time, 25.93M rss, 220.21M vsz</span>
<span class="c1"># Current date: Wed Jan  2 14:51:50 2019</span>
<span class="c1"># Hostname: localhost.localdomain</span>
<span class="c1"># Files: slow-query.log</span>
<span class="c1"># Overall: 2 total, 1 unique, 0.00 QPS, 0.01x concurrency ________________</span>
<span class="c1"># Time range: 2018-12-29T08:56:22 to 2018-12-29T09:04:54</span>
<span class="c1"># Attribute          total     min     max     avg     95%  stddev  median</span>
<span class="c1"># ============     ======= ======= ======= ======= ======= ======= =======</span>
<span class="c1"># Exec time             3s      1s      2s      2s      2s   707ms      2s</span>
<span class="c1"># Lock time              0       0       0       0       0       0       0</span>
<span class="c1"># Rows sent              2       1       1       1       1       0       1</span>
<span class="c1"># Rows examine           0       0       0       0       0       0       0</span>
<span class="c1"># Query size            30      15      15      15      15       0      15</span>

<span class="c1"># Profile</span>
<span class="c1"># Rank Query ID                           Response time Calls R/Call V/M</span>
<span class="c1"># ==== ================================== ============= ===== ====== =====</span>
<span class="c1">#    1 0x59A74D08D407B5EDF9A57DD5A41825CA 3.0015 100.0%     2 1.5007  0.33 SELECT</span>

<span class="c1"># Query 1: 0.00 QPS, 0.01x concurrency, ID 0x59A74D08D407B5EDF9A57DD5A41825CA at byte 565</span>
<span class="c1"># This item is included in the report because it matches --limit.</span>
<span class="c1"># Scores: V/M = 0.33</span>
<span class="c1"># Time range: 2018-12-29T08:56:22 to 2018-12-29T09:04:54</span>
<span class="c1"># Attribute    pct   total     min     max     avg     95%  stddev  median</span>
<span class="c1"># ============ === ======= ======= ======= ======= ======= ======= =======</span>
<span class="c1"># Count        100       2</span>
<span class="c1"># Exec time    100      3s      1s      2s      2s      2s   707ms      2s</span>
<span class="c1"># Lock time      0       0       0       0       0       0       0       0</span>
<span class="c1"># Rows sent    100       2       1       1       1       1       0       1</span>
<span class="c1"># Rows examine   0       0       0       0       0       0       0       0</span>
<span class="c1"># Query size   100      30      15      15      15      15       0      15</span>
<span class="c1"># String:</span>
<span class="c1"># Hosts        localhost</span>
<span class="c1"># Users        root</span>
<span class="c1"># Query_time distribution</span>
<span class="c1">#   1us</span>
<span class="c1">#  10us</span>
<span class="c1"># 100us</span>
<span class="c1">#   1ms</span>
<span class="c1">#  10ms</span>
<span class="c1"># 100ms</span>
<span class="c1">#    1s  ################################################################</span>
<span class="c1">#  10s+</span>
<span class="c1"># EXPLAIN /*!50100 PARTITIONS*/</span>
<span class="k">select</span> sleep<span class="o">(</span>2<span class="o">)</span><span class="se">\G</span>
-------------------------------------end-----------------------------------------
</code></pre></td></tr></table>
</div>
</div><h4 id="3pt分析慢查询">3、pt分析慢查询</h4>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt"> 1
</span><span class="lnt"> 2
</span><span class="lnt"> 3
</span><span class="lnt"> 4
</span><span class="lnt"> 5
</span><span class="lnt"> 6
</span><span class="lnt"> 7
</span><span class="lnt"> 8
</span><span class="lnt"> 9
</span><span class="lnt">10
</span><span class="lnt">11
</span><span class="lnt">12
</span><span class="lnt">13
</span><span class="lnt">14
</span><span class="lnt">15
</span><span class="lnt">16
</span><span class="lnt">17
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-shell" data-lang="shell">pt-query-digest  slow.log &gt; slow_report.log
pt-query-digest  --since<span class="o">=</span>12h  slow.log &gt; slow_report2.log
pt-query-digest slow.log --since <span class="s1">&#39;2014-04-17 09:30:00&#39;</span> --until <span class="s1">&#39;2014-04-17 10:00:00&#39;</span>&gt; &gt; slow_report3.log
pt-query-digest--filter <span class="s1">&#39;$event-&gt;{fingerprint} =~ m/^select/i&#39;</span> slow.log&gt;slow_report4.log
pt-query-digest--filter <span class="s1">&#39;($event-&gt;{user} || &#34;&#34;) =~ m/^root/i&#39;</span> slow.log&gt; slow_report5.log
pt-query-digest--filter <span class="s1">&#39;(($event-&gt;{Full_scan} || &#34;&#34;) eq &#34;yes&#34;) ||(($event-&gt;{Full_join} || &#34;&#34;) eq &#34;yes&#34;)&#39;</span> slow.log&gt; slow_report6.log
pt-query-digest  --user<span class="o">=</span>root –password<span class="o">=</span>abc123 --review  <span class="nv">h</span><span class="o">=</span>localhost,D<span class="o">=</span>test,t<span class="o">=</span>query_review --create-review-table  slow.log
pt-query-digest  --user<span class="o">=</span>root –password<span class="o">=</span>abc123 --review  <span class="nv">h</span><span class="o">=</span>localhost,D<span class="o">=</span>test,t<span class="o">=</span>query_ history--create-review-table  slow.log_20140401
pt-query-digest  --user<span class="o">=</span>root –password<span class="o">=</span>abc123--review  <span class="nv">h</span><span class="o">=</span>localhost,D<span class="o">=</span>test,t<span class="o">=</span>query_history--create-review-table  slow.log_20140402
<span class="c1"># 通过tcpdump抓取mysql的tcp协议数据，然后再分析</span>
tcpdump -s <span class="m">65535</span> -x -nn -q -tttt -i any -c <span class="m">1000</span> port <span class="m">3306</span> &gt; mysql.tcp.txt
pt-query-digest --type tcpdump mysql.tcp.txt&gt; slow_report9.log
<span class="c1"># 分析binlog</span>
mysqlbinlog mysql-bin.000093 &gt; mysql-bin000093.sql
pt-query-digest  --type<span class="o">=</span>binlog  mysql-bin000093.sql &gt; slow_report10.log
<span class="c1"># 分析general log</span>
pt-query-digest  --type<span class="o">=</span>genlog  localhost.log &gt; slow_report11.log
</code></pre></td></tr></table>
</div>
</div><h4 id="4pt校验主从">4、pt校验主从</h4>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt"> 1
</span><span class="lnt"> 2
</span><span class="lnt"> 3
</span><span class="lnt"> 4
</span><span class="lnt"> 5
</span><span class="lnt"> 6
</span><span class="lnt"> 7
</span><span class="lnt"> 8
</span><span class="lnt"> 9
</span><span class="lnt">10
</span><span class="lnt">11
</span><span class="lnt">12
</span><span class="lnt">13
</span><span class="lnt">14
</span><span class="lnt">15
</span><span class="lnt">16
</span><span class="lnt">17
</span><span class="lnt">18
</span><span class="lnt">19
</span><span class="lnt">20
</span><span class="lnt">21
</span><span class="lnt">22
</span><span class="lnt">23
</span><span class="lnt">24
</span><span class="lnt">25
</span><span class="lnt">26
</span><span class="lnt">27
</span><span class="lnt">28
</span><span class="lnt">29
</span><span class="lnt">30
</span><span class="lnt">31
</span><span class="lnt">32
</span><span class="lnt">33
</span><span class="lnt">34
</span><span class="lnt">35
</span><span class="lnt">36
</span><span class="lnt">37
</span><span class="lnt">38
</span><span class="lnt">39
</span><span class="lnt">40
</span><span class="lnt">41
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-shell" data-lang="shell"><span class="c1"># 创建账号</span>
create user <span class="s1">&#39;checksum&#39;</span>@<span class="s1">&#39;localhost&#39;</span> identified by <span class="s1">&#39;chk123!@#&#39;</span><span class="p">;</span>
GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO <span class="s1">&#39;checksum&#39;</span>@<span class="s1">&#39;192.168.0.%&#39;</span><span class="p">;</span>
create database percona<span class="p">;</span>
GRANT ALL PRIVILEGES ON percona.* TO <span class="s1">&#39;checksum&#39;</span>@<span class="s1">&#39;192.168.0.%&#39;</span><span class="p">;</span>
<span class="c1"># 默认创建表结构</span>
CREATE TABLE <span class="sb">`</span>checksums<span class="sb">`</span> <span class="o">(</span>
  <span class="sb">`</span>db<span class="sb">`</span> char<span class="o">(</span>64<span class="o">)</span> NOT NULL,
  <span class="sb">`</span>tbl<span class="sb">`</span> char<span class="o">(</span>64<span class="o">)</span> NOT NULL,
  <span class="sb">`</span>chunk<span class="sb">`</span> int<span class="o">(</span>11<span class="o">)</span> NOT NULL,
  <span class="sb">`</span>chunk_time<span class="sb">`</span> float DEFAULT NULL,
  <span class="sb">`</span>chunk_index<span class="sb">`</span> varchar<span class="o">(</span>200<span class="o">)</span> DEFAULT NULL,
  <span class="sb">`</span>lower_boundary<span class="sb">`</span> text,
  <span class="sb">`</span>upper_boundary<span class="sb">`</span> text,
  <span class="sb">`</span>this_crc<span class="sb">`</span> char<span class="o">(</span>40<span class="o">)</span> NOT NULL,
  <span class="sb">`</span>this_cnt<span class="sb">`</span> int<span class="o">(</span>11<span class="o">)</span> NOT NULL,
  <span class="sb">`</span>master_crc<span class="sb">`</span> char<span class="o">(</span>40<span class="o">)</span> DEFAULT NULL,
  <span class="sb">`</span>master_cnt<span class="sb">`</span> int<span class="o">(</span>11<span class="o">)</span> DEFAULT NULL,
  <span class="sb">`</span>ts<span class="sb">`</span> timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY <span class="o">(</span><span class="sb">`</span>db<span class="sb">`</span>,<span class="sb">`</span>tbl<span class="sb">`</span>,<span class="sb">`</span>chunk<span class="sb">`</span><span class="o">)</span>,
  KEY <span class="sb">`</span>ts_db_tbl<span class="sb">`</span> <span class="o">(</span><span class="sb">`</span>ts<span class="sb">`</span>,<span class="sb">`</span>db<span class="sb">`</span>,<span class="sb">`</span>tbl<span class="sb">`</span><span class="o">)</span>
<span class="o">)</span> <span class="nv">ENGINE</span><span class="o">=</span>InnoDB DEFAULT <span class="nv">CHARSET</span><span class="o">=</span>utf8
<span class="c1"># 校验数据库</span>
pt-table-checksum --host<span class="o">=</span>172.16.5.150 --port<span class="o">=</span><span class="m">3306</span> --user<span class="o">=</span>checksum --password<span class="o">=</span><span class="s1">&#39;chk123!@#&#39;</span> --no-check-binlog-format  --dababases<span class="o">=</span>db1
<span class="c1"># 校验数据库(忽略mysql库)</span>
pt-table-checksum --host<span class="o">=</span>localhost --port<span class="o">=</span><span class="m">3306</span> --user<span class="o">=</span>checksum --password<span class="o">=</span><span class="s1">&#39;chk123!@#&#39;</span> --no-check-binlog-format --nocheck-replication-filters --ignore-databases<span class="o">=</span>mysql,sys,percona
<span class="c1"># 主从修复测试</span>
pt-table-sync --replicate<span class="o">=</span>percona.checksums <span class="nv">h</span><span class="o">=</span>192.168.0.12,u<span class="o">=</span>checksum,p<span class="o">=</span><span class="s1">&#39;chk123!@#&#39;</span> <span class="nv">h</span><span class="o">=</span>192.168.0.14,u<span class="o">=</span>checksum,p<span class="o">=</span><span class="s1">&#39;chk123!@#&#39;</span> --print
<span class="c1"># 主从同步修复</span>
pt-table-sync --replicate<span class="o">=</span>percona.checksums <span class="nv">h</span><span class="o">=</span>192.168.0.12,u<span class="o">=</span>checksum,p<span class="o">=</span><span class="s1">&#39;chk123!@#&#39;</span> <span class="nv">h</span><span class="o">=</span>192.168.0.14,u<span class="o">=</span>checksum,p<span class="o">=</span><span class="s1">&#39;chk123!@#&#39;</span>  --execute
<span class="c1"># 只修复从库</span>
pt-table-sync --execute --replicate<span class="o">=</span>percona.checksums --sync-to-master <span class="nv">h</span><span class="o">=</span>172.16.5.151,P<span class="o">=</span>3306,u<span class="o">=</span>checksum,p<span class="o">=</span><span class="s1">&#39;chk123!@#&#39;</span>
<span class="c1"># 登录从库检查</span>
SELECT
*
FROM
percona.checksums
WHERE
master_cnt &lt;&gt; this_cnt
OR master_crc &lt;&gt; this_crc
OR ISNULL<span class="o">(</span>master_crc<span class="o">)</span> &lt;&gt; ISNULL<span class="o">(</span>this_crc<span class="o">)</span>
</code></pre></td></tr></table>
</div>
</div><h4 id="5pt热更新">5、pt热更新</h4>
<div class="highlight"><div class="chroma">
<table class="lntable"><tr><td class="lntd">
<pre class="chroma"><code><span class="lnt">  1
</span><span class="lnt">  2
</span><span class="lnt">  3
</span><span class="lnt">  4
</span><span class="lnt">  5
</span><span class="lnt">  6
</span><span class="lnt">  7
</span><span class="lnt">  8
</span><span class="lnt">  9
</span><span class="lnt"> 10
</span><span class="lnt"> 11
</span><span class="lnt"> 12
</span><span class="lnt"> 13
</span><span class="lnt"> 14
</span><span class="lnt"> 15
</span><span class="lnt"> 16
</span><span class="lnt"> 17
</span><span class="lnt"> 18
</span><span class="lnt"> 19
</span><span class="lnt"> 20
</span><span class="lnt"> 21
</span><span class="lnt"> 22
</span><span class="lnt"> 23
</span><span class="lnt"> 24
</span><span class="lnt"> 25
</span><span class="lnt"> 26
</span><span class="lnt"> 27
</span><span class="lnt"> 28
</span><span class="lnt"> 29
</span><span class="lnt"> 30
</span><span class="lnt"> 31
</span><span class="lnt"> 32
</span><span class="lnt"> 33
</span><span class="lnt"> 34
</span><span class="lnt"> 35
</span><span class="lnt"> 36
</span><span class="lnt"> 37
</span><span class="lnt"> 38
</span><span class="lnt"> 39
</span><span class="lnt"> 40
</span><span class="lnt"> 41
</span><span class="lnt"> 42
</span><span class="lnt"> 43
</span><span class="lnt"> 44
</span><span class="lnt"> 45
</span><span class="lnt"> 46
</span><span class="lnt"> 47
</span><span class="lnt"> 48
</span><span class="lnt"> 49
</span><span class="lnt"> 50
</span><span class="lnt"> 51
</span><span class="lnt"> 52
</span><span class="lnt"> 53
</span><span class="lnt"> 54
</span><span class="lnt"> 55
</span><span class="lnt"> 56
</span><span class="lnt"> 57
</span><span class="lnt"> 58
</span><span class="lnt"> 59
</span><span class="lnt"> 60
</span><span class="lnt"> 61
</span><span class="lnt"> 62
</span><span class="lnt"> 63
</span><span class="lnt"> 64
</span><span class="lnt"> 65
</span><span class="lnt"> 66
</span><span class="lnt"> 67
</span><span class="lnt"> 68
</span><span class="lnt"> 69
</span><span class="lnt"> 70
</span><span class="lnt"> 71
</span><span class="lnt"> 72
</span><span class="lnt"> 73
</span><span class="lnt"> 74
</span><span class="lnt"> 75
</span><span class="lnt"> 76
</span><span class="lnt"> 77
</span><span class="lnt"> 78
</span><span class="lnt"> 79
</span><span class="lnt"> 80
</span><span class="lnt"> 81
</span><span class="lnt"> 82
</span><span class="lnt"> 83
</span><span class="lnt"> 84
</span><span class="lnt"> 85
</span><span class="lnt"> 86
</span><span class="lnt"> 87
</span><span class="lnt"> 88
</span><span class="lnt"> 89
</span><span class="lnt"> 90
</span><span class="lnt"> 91
</span><span class="lnt"> 92
</span><span class="lnt"> 93
</span><span class="lnt"> 94
</span><span class="lnt"> 95
</span><span class="lnt"> 96
</span><span class="lnt"> 97
</span><span class="lnt"> 98
</span><span class="lnt"> 99
</span><span class="lnt">100
</span><span class="lnt">101
</span><span class="lnt">102
</span><span class="lnt">103
</span><span class="lnt">104
</span><span class="lnt">105
</span><span class="lnt">106
</span><span class="lnt">107
</span><span class="lnt">108
</span><span class="lnt">109
</span><span class="lnt">110
</span><span class="lnt">111
</span><span class="lnt">112
</span><span class="lnt">113
</span><span class="lnt">114
</span><span class="lnt">115
</span><span class="lnt">116
</span><span class="lnt">117
</span><span class="lnt">118
</span><span class="lnt">119
</span><span class="lnt">120
</span><span class="lnt">121
</span><span class="lnt">122
</span><span class="lnt">123
</span><span class="lnt">124
</span><span class="lnt">125
</span><span class="lnt">126
</span><span class="lnt">127
</span><span class="lnt">128
</span><span class="lnt">129
</span><span class="lnt">130
</span><span class="lnt">131
</span><span class="lnt">132
</span><span class="lnt">133
</span><span class="lnt">134
</span><span class="lnt">135
</span><span class="lnt">136
</span><span class="lnt">137
</span><span class="lnt">138
</span><span class="lnt">139
</span><span class="lnt">140
</span><span class="lnt">141
</span><span class="lnt">142
</span></code></pre></td>
<td class="lntd">
<pre class="chroma"><code class="language-shell" data-lang="shell"><span class="c1"># 参数说明</span>
--user:
-u，连接的用户名

--password：
-p，连接的密码

--database：
-D，连接的数据库

--port
-P，连接数据库的端口

--host:
-h，连接的主机地址

--socket:
-S，连接的套接字文件

--ask-pass
隐式输入连接MySQL的密码

--charset
指定修改的字符集

--defaults-file
-F，读取配置文件

--alter：
结构变更语句，不需要alter table关键字。可以指定多个更改，用逗号分隔。如下场景，需要注意：
    不能用RENAME来重命名表。
    列不能通过先删除，再添加的方式进行重命名，不会将数据拷贝到新列。
    如果加入的列非空而且没有默认值，则工具会失败。即其不会为你设置一个默认值，必须显示指定。
    删除外键<span class="o">(</span>drop foreign key constrain_name<span class="o">)</span>时，需要指定名称_constraint_name，而不是原始的constraint_name。
    如：CONSTRAINT <span class="sb">`</span>fk_foo<span class="sb">`</span> FOREIGN KEY <span class="o">(</span><span class="sb">`</span>foo_id<span class="sb">`</span><span class="o">)</span> REFERENCES <span class="sb">`</span>bar<span class="sb">`</span> <span class="o">(</span><span class="sb">`</span>foo_id<span class="sb">`</span><span class="o">)</span>，需要指定：--alter <span class="s2">&#34;DROP FOREIGN KEY _fk_foo&#34;</span>

--alter-foreign-keys-method
如何把外键引用到新表?需要特殊处理带有外键约束的表,以保证它们可以应用到新表.当重命名表的时候,外键关系会带到重命名后的表上。
该工具有两种方法,可以自动找到子表,并修改约束关系。
    auto： 在rebuild_constraints和drop_swap两种处理方式中选择一个。
    rebuild_constraints：使用 ALTER TABLE语句先删除外键约束,然后再添加.如果子表很大的话,会导致长时间的阻塞。
    drop_swap： <span class="nv">执行FOREIGN_KEY_CHECKS</span><span class="o">=</span>0,禁止外键约束,删除原表,再重命名新表。这种方式很快,也不会产生阻塞,但是有风险：
    1, 在删除原表和重命名新表的短时间内,表是不存在的,程序会返回错误。
    2, 如果重命名表出现错误,也不能回滚了.因为原表已经被删除。
    none： 类似<span class="s2">&#34;drop_swap&#34;</span>的处理方式,但是它不删除原表,并且外键关系会随着重命名转到老表上面。
--<span class="o">[</span>no<span class="o">]</span>check-alter
默认yes，语法解析。配合--dry-run 和 --print 一起运行，来检查是否有问题（change column，drop primary key）。

--max-lag
默认1s。每个chunk拷贝完成后，会查看所有复制Slave的延迟情况。要是延迟大于该值，则暂停复制数据，直到所有从的滞后小于这个值，使用Seconds_Behind_Master。如果有任何从滞后超过此选项的值，则该工具将睡眠--check-interval指定的时间，再检查。如果从被停止，将会永远等待，直到从开始同步，并且延迟小于该值。如果指定--check-slave-lag，该工具只检查该服务器的延迟，而不是所有服务器。

--check-slave-lag
指定一个从库的DSN连接地址,如果从库超过--max-lag参数设置的值,就会暂停操作。

--recursion-method
默认是show processlist，发现从的方法，也可以是host，但需要在从上指定report_host，通过show slave hosts来找到，可以指定none来不检查Slave。
METHOD       <span class="nv">USES</span>
<span class="o">===========</span>  <span class="o">==================</span>
processlist  SHOW PROCESSLIST
hosts        SHOW SLAVE HOSTS
<span class="nv">dsn</span><span class="o">=</span>DSN      DSNs from a table
none         Do not find slaves
指定none则表示不在乎从的延迟。
--check-interval
默认是1。--max-lag检查的睡眠时间。

--<span class="o">[</span>no<span class="o">]</span>check-plan
默认yes。检查查询执行计划的安全性。

--<span class="o">[</span>no<span class="o">]</span>check-replication-filters
默认yes。如果工具检测到服务器选项中有任何复制相关的筛选，如指定binlog_ignore_db和replicate_do_db此类。发现有这样的筛选，工具会报错且退出。因为如果更新的表Master上存在，而Slave上不存在，会导致复制的失败。使用–no-check-replication-filters选项来禁用该检查。

--<span class="o">[</span>no<span class="o">]</span>swap-tables
默认yes。交换原始表和新表，除非你禁止--<span class="o">[</span>no<span class="o">]</span>drop-old-table。

--<span class="o">[</span>no<span class="o">]</span>drop-triggers
默认yes，删除原表上的触发器。 --no-drop-triggers 会强制开启 --no-drop-old-table 即：不删除触发器就会强制不删除原表。

--new-table-name
复制创建新表的名称，默认%T_new。

--<span class="o">[</span>no<span class="o">]</span>drop-new-table
默认yes。删除新表，如果复制组织表失败。

--<span class="o">[</span>no<span class="o">]</span>drop-old-table
默认yes。复制数据完成重命名之后，删除原表。如果有错误则会保留原表。

--max-load
<span class="nv">默认为Threads_running</span><span class="o">=</span>25。每个chunk拷贝完后，会检查SHOW GLOBAL STATUS的内容，检查指标是否超过了指定的阈值。如果超过，则先暂停。这里可以用逗号分隔，指定多个条件，每个条件格式： <span class="nv">status指标</span><span class="o">=</span>MAX_VALUE或者status指标:MAX_VALUE。如果不指定MAX_VALUE，那么工具会这只其为当前值的120%。

--critical-load
<span class="nv">默认为Threads_running</span><span class="o">=</span>50。用法基本与--max-load类似，如果不指定MAX_VALUE，那么工具会这只其为当前值的200%。如果超过指定值，则工具直接退出，而不是暂停。

--default-engine
默认情况下，新的表与原始表是相同的存储引擎，所以如果原来的表使用InnoDB的，那么新表将使用InnoDB的。在涉及复制某些情况下，很可能主从的存储引擎不一样。使用该选项会默认使用默认的存储引擎。

--set-vars
设置MySQL变量，多个用逗号分割。默认该工具设置的是： <span class="nv">wait_timeout</span><span class="o">=</span><span class="m">10000</span> <span class="nv">innodb_lock_wait_timeout</span><span class="o">=</span><span class="m">1</span> <span class="nv">lock_wait_timeout</span><span class="o">=</span><span class="m">60</span>

--chunk-size-limit
当需要复制的块远大于设置的chunk-size大小,就不复制.默认值是4.0，一个没有主键或唯一索引的表,块大小就是不确定的。

--chunk-time
在chunk-time执行的时间内,动态调整chunk-size的大小,以适应服务器性能的变化，该参数设置为0,或者指定chunk-size,都可以禁止动态调整。

--chunk-size
指定块的大小,默认是1000行,可以添加k,M,G后缀.这个块的大小要尽量与--chunk-time匹配，如果明确指定这个选项,那么每个块就会指定行数的大小.

--<span class="o">[</span>no<span class="o">]</span>check-plan
默认yes。为了安全,检查查询的执行计划.默认情况下,这个工具在执行查询之前会先EXPLAIN,以获取一次少量的数据,如果是不好的EXPLAIN,那么会获取一次大量的数据，这个工具会多次执行EXPALIN,如果EXPLAIN不同的结果,那么就会认为这个查询是不安全的。

--statistics
打印出内部事件的数目，可以看到复制数据插入的数目。

--dry-run
创建和修改新表，但不会创建触发器、复制数据、和替换原表。并不真正执行，可以看到生成的执行语句，了解其执行步骤与细节。--dry-run与--execute必须指定一个，二者相互排斥。和--print配合最佳。

--execute
确定修改表，则指定该参数。真正执行。--dry-run与--execute必须指定一个，二者相互排斥。

--print
打印SQL语句到标准输出。指定此选项可以让你看到该工具所执行的语句，和--dry-run配合最佳。

--progress
复制数据的时候打印进度报告，二部分组成：第一部分是百分比，第二部分是时间。

--quiet
-q，不把信息标准输出。
<span class="c1"># 增加字段</span>
pt-online-schema-change --user<span class="o">=</span>root --host<span class="o">=</span>172.16.5.150  --alter <span class="s2">&#34;ADD COLUMN content text TINYINT NOT NULL DEFAULT 0 &#34;</span> <span class="nv">D</span><span class="o">=</span>test,t<span class="o">=</span><span class="nb">test</span> --ask-pass --no-check-replication-filters --alter-foreign-keys-method<span class="o">=</span>auto --recursion-method<span class="o">=</span>none --print --execute
<span class="c1"># 删除字段</span>
pt-online-schema-change --user<span class="o">=</span>root --host<span class="o">=</span>172.16.5.150  --alter <span class="s2">&#34;DROP COLUMN content &#34;</span> <span class="nv">D</span><span class="o">=</span>aaa,t<span class="o">=</span><span class="nb">test</span> --ask-pass --no-check-replication-filters --alter-foreign-keys-method<span class="o">=</span>auto --recursion-method<span class="o">=</span>none --print --execute
<span class="c1"># 修改字段</span>
pt-online-schema-change --user<span class="o">=</span>root --host<span class="o">=</span>172.16.5.150  --alter <span class="s2">&#34;MODIFY COLUMN content TINYINT NOT NULL DEFAULT 1&#34;</span> <span class="nv">D</span><span class="o">=</span>test,t<span class="o">=</span><span class="nb">test</span> --no-check-replication-filters --alter-foreign-keys-method<span class="o">=</span>auto --recursion-method<span class="o">=</span>none --print --execute
<span class="c1"># 字段改名</span>
pt-online-schema-change --user<span class="o">=</span>root --host<span class="o">=</span>172.16.5.150  --alter <span class="s2">&#34;CHANGE COLUMN age address varchar(30)&#34;</span> <span class="nv">D</span><span class="o">=</span>test,t<span class="o">=</span><span class="nb">test</span>  --ask-pass  --no-check-alter --no-check-replication-filters --alter-foreign-keys-method<span class="o">=</span>auto --recursion-method<span class="o">=</span>none --print --execute
<span class="c1"># 增加索引</span>
pt-online-schema-change --user<span class="o">=</span>root --host<span class="o">=</span>172.16.5.150  --alter <span class="s2">&#34;ADD INDEX idx_address(id_number)&#34;</span> <span class="nv">D</span><span class="o">=</span>test,t<span class="o">=</span><span class="nb">test</span>  --ask-pass  --no-check-alter --no-check-replication-filters --alter-foreign-keys-method<span class="o">=</span>auto --recursion-method<span class="o">=</span>none --print --execute
<span class="c1"># 删除索引</span>
pt-online-schema-change --user<span class="o">=</span>root --host<span class="o">=</span>172.16.5.150  --alter <span class="s2">&#34;DROP INDEX idx_address&#34;</span> <span class="nv">D</span><span class="o">=</span>test,t<span class="o">=</span><span class="nb">test</span> --no-check-alter --no-check-replication-filters --alter-foreign-keys-method<span class="o">=</span>auto --recursion-method<span class="o">=</span>none --no-drop-old-table --print --execute
<span class="c1"># 修改存储引擎</span>
pt-online-schema-change --user<span class="o">=</span>root --host<span class="o">=</span>192.168.0.12  --alter <span class="s2">&#34;ENGINE = InnoDB&#34;</span> <span class="nv">D</span><span class="o">=</span>society_insurance_storage,t<span class="o">=</span>user --ask-pass --no-check-alter --no-check-replication-filters --alter-foreign-keys-method<span class="o">=</span>auto --recursion-method<span class="o">=</span>none --no-drop-old-table --print --execute
</code></pre></td></tr></table>
</div>
</div></div><div id="comments"></div></div></div>
            </main><footer class="footer">
        <div class="footer-container"><div class="footer-line"><i class="far fa-copyright fa-fw"></i><span itemprop="copyrightYear">2019 - 2021</span><span class="author" itemprop="copyrightHolder">&nbsp;<a href="/" target="_blank">mikel pan</a></span>&nbsp;|&nbsp;<span class="license"><a rel="license external nofollow noopener noreffer" href="https://creativecommons.org/licenses/by-nc/4.0/" target="_blank">CC BY-NC 4.0</a></span><span class="icp-splitter">&nbsp;|&nbsp;</span><br class="icp-br"/>
                    <span class="icp"><a href="https://beian.miit.gov.cn/" target="_blank">粤ICP备2021047442号</a></span></div>
        </div>
    </footer></div>

        <div id="fixed-buttons"><a href="#" id="back-to-top" class="fixed-button" title="回到顶部">
                <i class="fas fa-arrow-up fa-fw"></i>
            </a><a href="#" id="view-comments" class="fixed-button" title="查看评论">
                <i class="fas fa-comment fa-fw"></i>
            </a>
        </div><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/lightgallery.js@1.2.0/dist/css/lightgallery.min.css"><script type="text/javascript" src="https://cdn.jsdelivr.net/npm/smooth-scroll@16.1.3/dist/smooth-scroll.min.js"></script><script type="text/javascript" src="https://cdn.jsdelivr.net/npm/autocomplete.js@0.37.1/dist/autocomplete.min.js"></script><script type="text/javascript" src="https://cdn.jsdelivr.net/npm/algoliasearch@4.2.0/dist/algoliasearch-lite.umd.min.js"></script><script type="text/javascript" src="https://cdn.jsdelivr.net/npm/lazysizes@5.2.2/lazysizes.min.js"></script><script type="text/javascript" src="https://cdn.jsdelivr.net/npm/lightgallery.js@1.2.0/dist/js/lightgallery.min.js"></script><script type="text/javascript" src="https://cdn.jsdelivr.net/npm/lg-thumbnail.js@1.2.0/dist/lg-thumbnail.min.js"></script><script type="text/javascript" src="https://cdn.jsdelivr.net/npm/lg-zoom.js@1.2.0/dist/lg-zoom.min.js"></script><script type="text/javascript" src="https://cdn.jsdelivr.net/npm/clipboard@2.0.6/dist/clipboard.min.js"></script><script type="text/javascript" src="https://cdn.jsdelivr.net/npm/sharer.js@0.4.0/sharer.min.js"></script><script type="text/javascript">window.config={"code":{"copyTitle":"复制到剪贴板","maxShownLines":10},"comment":{},"lightGallery":{"actualSize":false,"exThumbImage":"data-thumbnail","hideBarsDelay":2000,"selector":".lightgallery","speed":400,"thumbContHeight":80,"thumbWidth":80,"thumbnail":true},"search":{"algoliaAppID":"REQJX89W85","algoliaIndex":"index.zh-cn","algoliaSearchKey":"63fa048de9b35627f46672e95abc14df","highlightTag":"em","maxResultLength":10,"noResultsFound":"没有找到结果","snippetLength":50,"type":"algolia"}};</script><script type="text/javascript" src="/js/theme.min.js"></script></body>
</html>
